package Dao;

import Bean.Flight;
import Bean.User;
import Utils.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;

/**
 * Created with IntelliJ IDEA.
 * Description:
 * User: 张
 * Date: 2025-02-26
 * Time: 22:04
 */
public class FlightDao {
    private Connection connection = null;
    private PreparedStatement statement = null;
    private ResultSet resultSet = null;

    //1.新增航班
    public boolean insert(Flight flight) {
        try {
            //1.和数据库建立连接
            connection = DBUtil.getConnection();
            //2.构造sql语句
            String sql = "insert into flight values(?,?,?,?,?,?,?)";
            statement = connection.prepareStatement(sql);
            statement.setString(1,flight.getFlightId());
            statement.setString(2,flight.getAircraftId());
            statement.setObject(3,flight.getDepartureTime());
            statement.setObject(4,flight.getArrivalTime());
            statement.setString(5,flight.getOrigin());
            statement.setString(6,flight.getDestination());
            statement.setDouble(7,flight.getPrice());
            //3.执行sql
            int ret = statement.executeUpdate();
            if(ret == 1) {
//                System.out.println("[FlightDao] 新增用户成功");
                return true;
            } else {
//                System.out.println("[FlightDao] 新增用户失败!!");
                return false;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection,statement,null);
        }
        return false;
    }

    //2.删除航班
    public boolean delete(Flight flight) {
        try {
            //1.和数据库建立连接
            connection = DBUtil.getConnection();
            //2.拼装sql语句
            String sql = "delete from flight where flight_id = ?";
            statement = connection.prepareStatement(sql);
            statement.setString(1,flight.getFlightId());
            //3.执行sql
            int ret = statement.executeUpdate();
            if(ret == 1) {
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection,statement,null);
        }
        return false;
    }


    //3.航班查询的时候分为两种
    //1).为航班列表,这里查询的参数只要起飞时间,起飞地点和起飞地点 --- 在查询航班的时候要确实该航班所使用的飞机时正常状态的
    public List<Flight> selectFlights(int pageNo , int pageSize ,String start, String end, LocalDateTime dbDateStr) {
        List<Flight> array = null;
        try{
            connection = DBUtil.getConnection();
            //构建查询语句 -- 需要查询的元素为 航班号,起飞时间,到达时间,出发将机场,降落机场

            String sql = "select f.flight_id,f.departure_time,f.arrival_time,f.origin,f.destination from flight f join aircraft a ON f.aircraft_id = a.aircraft_id " +
                    "where DATE(f.departure_time) = ? and f.origin = ? and f.destination = ? and a.current_status = '可用' Limit ?,?";
            statement=connection.prepareStatement(sql);
            statement.setObject(1,dbDateStr);
            statement.setString(2,start);
            statement.setObject(3,end);
            statement.setInt(4,(pageNo - 1) * pageSize);
            statement.setInt(5,pageSize);
            //指向sql
            resultSet = statement.executeQuery();
            //读取数据
            if (resultSet != null) {
                array = new ArrayList<>();
                while(resultSet.next()) {
                    Flight flight1 = new Flight();
                    flight1.setFlightId(resultSet.getString("flight_id"));
                    flight1.setDepartureTime(resultSet.getObject("departure_time", LocalDateTime.class));
                    flight1.setArrivalTime(resultSet.getObject("arrival_time", LocalDateTime.class));
                    flight1.setOrigin(resultSet.getString("origin"));
                    flight1.setDestination(resultSet.getString("destination"));
                    array.add(flight1);
                }
            }
        }catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,statement,resultSet);
        }
        return array;
    }

    public List<Flight> selectFlights(int pageNo,int pageSize) {
        List<Flight> array = null;
        try{
            connection = DBUtil.getConnection();
            //构建查询语句 -- 需要查询的元素为 航班号,起飞时间,到达时间,出发将机场,降落机场

            String sql = "select * from flight f join aircraft a ON f.aircraft_id = a.aircraft_id " +
                    "where a.current_status = '可用' Limit ?,?";
            statement=connection.prepareStatement(sql);

            statement.setInt(1,(pageNo - 1) * pageSize);
            statement.setInt(2,pageSize);
            //指向sql
            resultSet = statement.executeQuery();
            //读取数据
            if (resultSet != null) {
                array = new ArrayList<>();
                while(resultSet.next()) {
                    Flight flight1 = new Flight();
                    flight1.setFlightId(resultSet.getString("flight_id"));
                    DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm");
                    flight1.setDepartureTime(resultSet.getObject("departure_time", LocalDateTime.class));
                    //读取航班数据的时候要把时间的格式给修改一下
                    flight1.setAircraftId(resultSet.getString("aircraft_id"));
                    flight1.setArrivalTime(resultSet.getObject("arrival_time", LocalDateTime.class));
                    flight1.setOrigin(resultSet.getString("origin"));
                    flight1.setDestination(resultSet.getString("destination"));
                    flight1.setPrice(resultSet.getDouble("price"));
                    array.add(flight1);
                }
            }
        }catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,statement,resultSet);
        }
        return array;
    }
    //2).查询单个航班详细信息
    public Flight selectByFlightId (Flight flight) {
        Flight flight1 = null;
        try {
            connection = DBUtil.getConnection();

            String sql = "select * from flight where flight_id = ?";
            statement=connection.prepareStatement(sql);
            statement.setString(1,flight.getFlightId());
            resultSet = statement.executeQuery();
            //读取数据
            if(resultSet.next()) {
                flight1 = new Flight();
                flight1.setFlightId(resultSet.getString("flight_id"));
                flight1.setAircraftId(resultSet.getString("aircraft_id"));
                flight1.setDepartureTime(resultSet.getObject("departure_time", LocalDateTime.class));
                flight1.setArrivalTime(resultSet.getObject("arrival_time", LocalDateTime.class));
                flight1.setOrigin(resultSet.getString("origin"));
                flight1.setDestination(resultSet.getString("destination"));
                flight1.setPrice(resultSet.getDouble("price"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,statement,resultSet);
        }
        return flight1;
    }

    //4.航班数据的修改
    public boolean update(Flight flight) {
        try {
            //1.和数据库建立连接
            connection = DBUtil.getConnection();
            String sql="UPDATE flight SET aircraft_id = ?,departure_time = ?,arrival_time = ?,origin = ?, destination = ? ,price = ? WHERE flight_id=?";
            statement=connection.prepareStatement(sql);
            statement.setString(1,flight.getAircraftId());
            statement.setObject(2,flight.getDepartureTime());
            statement.setObject(3,flight.getArrivalTime());
            statement.setString(4,flight.getOrigin());
            statement.setString(5,flight.getDestination());
            statement.setDouble(6,flight.getPrice());
            statement.setString(7,flight.getFlightId());
            int ret = statement.executeUpdate();
            if(ret == 1) {
                System.out.println("[FlightDao] 修改数据成功!!");
                return true;
            } else {
                System.out.println("[FlightDao] 修改数据失败!!");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection,statement,null);
        }
        return false;
    }

    //指定起飞地,目的地,时间的航班查询 -- 数目
    public int size(String start, String end, LocalDateTime dbDateStr) {
        int size = 0;
        try{
            System.out.println("[FlightDao] start = " + start + ",end = " + end + " ,dbDateStr = " + dbDateStr);
            //1.和数据库建立连接
            connection = DBUtil.getConnection();
            //2.构造sql语句
            String sql = "SELECT COUNT(*) AS flight_count FROM flight where origin = ? and destination = ? and DATE(departure_time) = ?";
            statement = connection.prepareStatement(sql);
            statement.setString(1,start);
            statement.setString(2,end);
            statement.setObject(3,dbDateStr);
            resultSet = statement.executeQuery();
            if(resultSet.next()) {
                size = resultSet.getInt("flight_count");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection,statement,resultSet);
        }
        return size;
    }

    public int size() {
        int size = 0;
        try{

            //1.和数据库建立连接
            connection = DBUtil.getConnection();
            //2.构造sql语句
            String sql = "SELECT COUNT(*) AS flight_count FROM flight";
            statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery();
            if(resultSet.next()) {
                size = resultSet.getInt("flight_count");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection,statement,resultSet);
        }
        return size;
    }


}
